package dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import dao.BillDAO;
import imageUtil.Page;
import model.Bill;
import util.DBConnection;

public class BillDAOImp implements BillDAO {
	
	private static final String findByCount = "select count(*) from bills where vendor=?";
	
	private static final String findCount = "select count(*) from bills where vendor=? and whether_to_pay=?";
	
	private static final String findByAll = "select * from bills where vendor=? limit ?,?";
	
	private static final String findAllByPay = "select * from bills where vendor=? and whether_to_pay=? limit ?,?";
	
	private static final String updateBill = "update bills set whether_to_pay=?,payment_method=?,cheque_number=? where id=?";
	
	private static final String insertBill = "insert into bills(vendor,bill_date,bill_due_date,sum) values(?,?,?,?)";

	@Override
	public int findByCount(String vendorName) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		int count= 0;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByCount);
			prep.setString(1, vendorName);
			rst= prep.executeQuery();
			if(rst.next()) {
				count=rst.getInt(1);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return count; 
	}

	@Override
	public List<Bill> findByAll(Page page, String name) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Bill> reve = new ArrayList<Bill>();
		Bill i= null;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByAll);
			prep.setString(1, name);
			prep.setInt(2, page.getBeginIndex());
			prep.setInt(3, page.getEveryPage());
			rst= prep.executeQuery();
			while(rst.next()) {
				i= new Bill();
				i.setId(rst.getInt("id"));
				i.setVendor(rst.getString("vendor"));
				i.setBillDate(rst.getDate("bill_date"));
				i.setBillDueDate(rst.getDate("bill_due_date"));
				i.setSum(rst.getInt("sum"));
				i.setWhetherToPay(rst.getString("whether_to_pay"));
				i.setPaymentMethod(rst.getString("payment_method"));
				i.setChequeNumber(rst.getString("cheque_number"));
				reve.add(i);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return reve; 
	}

	@Override
	public boolean updateBill(Bill bill) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(updateBill);
			prep.setString(1, bill.getWhetherToPay());
			prep.setString(2, bill.getPaymentMethod());
			prep.setString(3, bill.getChequeNumber());
			prep.setInt(4, bill.getId());
			i = prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.close(prep, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(i != 0){
			return true;
		}else {
			return false;
		}	
	}

	@Override
	public List<Bill> findByAll(Page page, String name, String whetherToPay) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Bill> reve = new ArrayList<Bill>();
		Bill i= null;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findAllByPay);
			prep.setString(1, name);
			prep.setString(2, whetherToPay);
			prep.setInt(3, page.getBeginIndex());
			prep.setInt(4, page.getEveryPage());
			rst= prep.executeQuery();
			while(rst.next()) {
				i= new Bill();
				i.setId(rst.getInt("id"));
				i.setVendor(rst.getString("vendor"));
				i.setBillDate(rst.getDate("bill_date"));
				i.setBillDueDate(rst.getDate("bill_due_date"));
				i.setSum(rst.getInt("sum"));
				i.setWhetherToPay(rst.getString("whether_to_pay"));
				i.setPaymentMethod(rst.getString("payment_method"));
				i.setChequeNumber(rst.getString("cheque_number"));
				reve.add(i);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return reve; 
	}

	@Override
	public boolean saveBill(Bill bill) throws Exception {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(insertBill);
			prep.setString(1, bill.getVendor());
			prep.setDate(2, new java.sql.Date(bill.getBillDate().getTime()));
			prep.setDate(3, new java.sql.Date(bill.getBillDueDate().getTime()));
			prep.setInt(4, bill.getSum());
			i = prep.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					DBConnection.close(prep, conn);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		if(i != 0){
			return true;
		}else {
			return false;
		}
	}

	@Override
	public int findCount(String vendorName, String whetherToPay) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		int count= 0;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findCount);
			prep.setString(1, vendorName);
			prep.setString(2, whetherToPay);
			rst= prep.executeQuery();
			if(rst.next()) {
				count=rst.getInt(1);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return count; 
	}

}
